package com.administration.dao.impl;

import java.sql.Connection;
import java.util.List;

import javax.persistence.EntityManager;

import com.administration.dao.AdministrationDao;
import com.administration.entity.Access;
import com.administration.entity.Category;
import com.administration.entity.Groups;
import com.administration.entity.GroupPrivilege;
import com.administration.entity.Privilege;
import com.administration.entity.User;
import com.administration.entity.resultsetmapping.PrivilegeMapping;
import com.core.dao.impl.BaseDAOImpl;

public class AdministrationDaoImpl extends BaseDAOImpl implements
		AdministrationDao{
	private static final long serialVersionUID = 1L;

	Connection connection;

	public AdministrationDaoImpl() {

	}

	public Long addAccess(Access access) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.persist(access);
			em.getTransaction().commit();
			return access.getRecid();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// return null;
		// connection = Connecting.getConnection();
		// String sql = "insert into access(access_name) " + "values(" + "'"
		// + access.getAccessName() + "')";
		// Statement statement = connection.createStatement();
		// statement.executeUpdate(sql);
		// ResultSet rs = statement.getGeneratedKeys();
		// Integer currentRecId = -1;
		// if (rs.next()) {
		// currentRecId = rs.getInt(1);
		// }
		// connection.commit();
		// return (long) currentRecId;
	}

	public void updateAccess(Access access) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.merge(access);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "update access set access_name=? where recid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		//
		// ps.setString(1, access.getAccessName());
		// ps.setLong(2, access.getRecid());
		// ps.executeUpdate();
		// connection.commit();
	}

	public void delateAccess(Access access) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.remove(access);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "delete from access where recid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		//
		// ps.setLong(1, access.getRecid());
		// ps.executeUpdate();
		// connection.commit();

	}

	public List<Access> getAllAccess() throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select a from Access a";
		return em.createQuery(sql).getResultList();

		// connection = Connecting.getConnection();
		// String sql = "select * from access order by recid ASC";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		// List<Access> allAccessList = new ArrayList<Access>();
		//
		// while (rs.next() == true) {
		// Access access = new Access();
		// access.setRecid(rs.getLong("recid"));
		// access.setAccessName(rs.getString("access_name"));
		// allAccessList.add(access);
		// }
		//
		// return allAccessList;
	}

	// --------------------------------------------------------------//
	// ------------------------Privilege-----------------------------//
	// --------------------------------------------------------------//

	public Long addPrivilege(Privilege privilage) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.persist(privilage);
			em.getTransaction().commit();
			return privilage.getRecid();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "insert into privileges(name,category_id) " + "values("
		// + "'" + privilage.getName() + "'," + privilage.getCategoryId()
		// + ")";
		// Statement statement = connection.createStatement();
		// statement.executeUpdate(sql);
		// ResultSet rs = statement.getGeneratedKeys();
		// Integer currentRecId = -1;
		// if (rs.next()) {
		// currentRecId = rs.getInt(1);
		// }
		// connection.commit();
		// return (long) currentRecId;
	}

	public void updatePrivilege(Privilege privilage) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.merge(privilage);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql =
		// "update privileges set name=?,category_id=? where recid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setString(1, privilage.getName());
		// ps.setLong(2, privilage.getCategoryId());
		// ps.setLong(3, privilage.getRecid());
		// ps.executeUpdate();
		// connection.commit();
	}

	public void delatePrivilege(Privilege privilage) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.remove(privilage);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "delete from privileges where recid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, privilage.getRecid());
		// ps.executeUpdate();
		// connection.commit();
	}

	public List<Privilege> getAllPrivileges() throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select p from Privilege p";
		return em.createQuery(sql).getResultList();

		// return super.getEntityList(Privilege.class, "recid");
		// connection = Connecting.getConnection();
		// String sql = "select * from privileges order by recid ASC";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		// List<Privilege> allPrivilageList = new ArrayList<Privilege>();
		//
		// while (rs.next() == true) {
		// Privilege privilege = new Privilege();
		// privilege.setRecid(rs.getLong("recid"));
		// privilege.setName(rs.getString("name"));
		// privilege.setCategoryId(rs.getLong("category_id"));
		// allPrivilageList.add(privilege);
		// }
		//
		// return allPrivilageList;
	}

	// ----------------------------------------------------------//
	// ------------------------GroupPrivileges-------------------//
	// ----------------------------------------------------------//

	public Long addGroupPrivileges(GroupPrivilege groupPrivilages)
			throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.persist(groupPrivilages);
			em.getTransaction().commit();
			return groupPrivilages.getRecid();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql =
		// "insert into group_privileges(group_id,privilage_id,access_id) "
		// + "values("
		// + groupPrivilages.getGroupId()
		// + " , "
		// + groupPrivilages.getPrivilageId()
		// + " , "
		// + groupPrivilages.getAccessId() + ")";
		// Statement statement = connection.createStatement();
		// statement.executeUpdate(sql);
		// ResultSet rs = statement.getGeneratedKeys();
		// Integer currentRecId = -1;
		// if (rs.next()) {
		// currentRecId = rs.getInt(1);
		// }
		// connection.commit();
		// return (long) currentRecId;
	}

	public void updateGroupPrivileges(GroupPrivilege groupPrivilages)
			throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.merge(groupPrivilages);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql =
		// "update group_privileges set access_id=? where group_id=? and privilage_id=? ";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupPrivilages.getAccessId());
		// ps.setLong(2, groupPrivilages.getGroupId());
		// ps.setLong(3, groupPrivilages.getPrivilageId());
		// ps.executeUpdate();
		// connection.commit();
	}

	public void delateGroupPrivileges(GroupPrivilege groupPrivilages)
			throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.remove(groupPrivilages);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}

		// connection = Connecting.getConnection();
		// String sql =
		// "delete from group_privileges  where group_id=? and privilage_id=? ";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupPrivilages.getGroupId());
		// ps.setLong(2, groupPrivilages.getPrivilageId());
		// ps.executeUpdate();
		// connection.commit();

	}

	// public Boolean isGroupPrivilagesFound(GroupPrivilege groupPrivilege)
	// throws Exception {
	// connection = Connecting.getConnection();
	// String sql =
	// "select * from group_privileges where group_id=? and privilage_id=? and access_id=?";
	// PreparedStatement ps = connection.prepareStatement(sql);
	// ps.setLong(1, groupPrivilege.getGroupId());
	// ps.setLong(2, groupPrivilege.getPrivilageId());
	// ps.setLong(3, groupPrivilege.getAccessId());
	// ResultSet rs = ps.executeQuery();
	//
	// if (rs.next() == true) {
	// return Boolean.TRUE;
	// }
	// return Boolean.FALSE;
	//
	// }

	public List<GroupPrivilege> getAllGroupPrivileges(Long groupRecId)
			throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select gp from GroupPrivilege gp where gp.groupId=:groupId";
		return em.createQuery(sql).setParameter("groupId", groupRecId)
				.getResultList();

		// return super.getEntityList(GroupPrivilege.class, "groupId",
		// groupRecId,
		// null);
		// connection = Connecting.getConnection();
		// String sql =
		// "select * from group_privileges where group_id=? order by privilage_id ASC";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupRecId);
		// ResultSet rs = ps.executeQuery();
		// List<GroupPrivilege> allGroupPrivilagesList = new
		// ArrayList<GroupPrivilege>();
		//
		// while (rs.next() == true) {
		// GroupPrivilege groupPrivilages = new GroupPrivilege();
		// groupPrivilages.setAccessId(rs.getLong("access_id"));
		// groupPrivilages.setGroupId(rs.getLong("group_id"));
		// groupPrivilages.setPrivilageId(rs.getLong("privilage_id"));
		// allGroupPrivilagesList.add(groupPrivilages);
		// }
		//
		// return allGroupPrivilagesList;
	}

	// @Transactional
	public void deleteAllGroupPrivilegesOfGroup(Long groupId) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.createQuery(
					"delete from GroupPrivilege gp where gp.groupId=:groupId")
					.setParameter("groupId", groupId).executeUpdate();
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "delete from group_privileges  where group_id=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupId);
		// ps.executeUpdate();
		// connection.commit();

	}

	// ----------------------------------------------------------//
	// -------------------------Group----------------------------//
	// ----------------------------------------------------------//

	public Long addGroup(Groups group) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.persist(group);
			em.getTransaction().commit();
			return group.getRecid();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}

		// connection = Connecting.getConnection();
		// String sql = "insert into groups(groupname,groupstatus) " + "values("
		// + "'" + group.getGroupName() + "' ,'" + group.getGroupStatus()
		// + " '" + ")";
		// Statement statement = connection.createStatement();
		// statement.executeUpdate(sql);
		// ResultSet rs = statement.getGeneratedKeys();
		// Integer currentRecId = -1;
		// if (rs.next()) {
		// currentRecId = rs.getInt(1);
		// }
		// connection.commit();
		//
		// return (long) currentRecId;

	}

	public Groups getGroupById(Long groupId) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select g from Group g where g.groupId=:groupId";
		return (Groups) em.createQuery(sql).setParameter("groupId", groupId)
				.getSingleResult();

		// return super.getEntity(Group.class, groupId);
		// connection = Connecting.getConnection();
		// String sql =
		// "select * from groups  where groupid=? order by groupid ASC";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupId);
		//
		// ResultSet rs = ps.executeQuery();
		// Group group = null;
		// if (rs.next()) {
		// group = new Group();
		// group.setGroupId(rs.getLong("groupid"));
		// group.setGroupName(rs.getString("groupname"));
		// group.setGroupStatus(rs.getLong("groupstatus"));
		// }
		// return group;
	}

	public List<Groups> getAllGroups() throws Exception {

		EntityManager em = getEntityManager();
		String sql = "select g from Group g ";
		return em.createQuery(sql).getResultList();

		// return super.getEntityList(Group.class, null);

		// connection = Connecting.getConnection();
		// String sql = "select * from groups order by groupid ASC";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		// List<Group> groupsList = new ArrayList<Group>();
		// while (rs.next()) {
		// Group group = new Group();
		//
		// group.setGroupId(rs.getLong("groupid"));
		// group.setGroupName(rs.getString("groupname"));
		// group.setGroupStatus(rs.getLong("groupstatus"));
		//
		// groupsList.add(group);
		// }
		// return groupsList;
	}

	public void deleteGroup(Groups group) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.remove(group);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "DELETE FROM groups WHERE groupid =" +
		// group.getGroupId();
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.executeUpdate();
		// connection.commit();

	}

	public void updateGroup(Groups group) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.merge(group);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql =
		// "update groups set groupname=?, groupstatus=? where groupid =?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		//
		// ps.setString(1, group.getGroupName());
		// ps.setLong(2, group.getGroupStatus());
		// ps.setLong(3, group.getGroupId());
		//
		// ps.executeUpdate();
		// connection.commit();
	}

	// ----------------------------------------------------------//
	// -------------------------Category-------------------------//
	// ----------------------------------------------------------//
	public Category getCategoryById(Long categoryId) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select c from Category c where c.categoryId=:categoryId";
		return (Category) em.createQuery(sql)
				.setParameter("categoryId", categoryId).getSingleResult();

		// return super.getEntity(Category.class, "categoryId", categoryid);
		// connection = Connecting.getConnection();
		//
		// String sql = "select * from categories  where categoryid=?";
		//
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, categoryid);
		//
		// ResultSet rs = ps.executeQuery();
		// if (rs.next()) {
		// Category category = new Category();
		// category.setCategoryId(rs.getLong("categoryid"));
		// category.setCategoryName(rs.getString("categoryname"));
		// category.setDescription(rs.getString("description"));
		// return category;
		// }
		// return null;
	}

	public List<Category> getAllCategories() throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select c from Category c ";
		return em.createQuery(sql).getResultList();

		// return super.getEntityList(Category.class, null);

		// connection = Connecting.getConnection();
		// String sql = "select * from categories";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		//
		// List<Category> categoryList = new ArrayList<Category>();
		// while (rs.next()) {
		// Category category = new Category();
		//
		// category.setCategoryId(rs.getLong("categoryid"));
		// category.setCategoryName(rs.getString("categoryname"));
		// category.setDescription(rs.getString("description"));
		// categoryList.add(category);
		//
		// }
		// return categoryList;
	}

	// ----------------------------------------------------------//
	// ---------------------------User---------------------------//
	// ----------------------------------------------------------//
	public Long addUser(User user) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.persist(user);
			em.getTransaction().commit();
			return user.getUserId();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}

		// connection = Connecting.getConnection();
		// Statement stmt = null;
		// ResultSet rs = null;
		// stmt = connection.createStatement();
		//
		// String sql =
		// "insert into USERS (username,password,groupid,firstname,secondname,email,mobile)"
		// + " values("
		// + "'"
		// + user.getUserName()
		// + "',"
		// + "'"
		// + user.getPassword().replace("'", "''")
		// + "',"
		// + ""
		// + user.getGroupId()
		// + ","
		// + "'"
		// + user.getFirstName()
		// + "',"
		// + "'"
		// + user.getSecondName()
		// + "',"
		// + "'"
		// + user.getEmail()
		// + "'," + "'" + user.getMobile() + "'" + ")";
		// stmt.executeUpdate(sql);
		// int autoIncKeyFromApi = -1;
		// rs = stmt.getGeneratedKeys();
		//
		// if (rs.next()) {
		// autoIncKeyFromApi = rs.getInt(1);
		// }
		// connection.commit();
		// stmt.close();
		// rs.close();
		// stmt = null;
		// rs = null;
		//
		// return (long) autoIncKeyFromApi;

	}

	public void updateUser(User user) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.merge(user);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql =
		// "update users set username=?,password=?,groupid=?,firstname=?,secondname=?,email=?,mobile=?  where userid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		//
		// ps.setString(1, user.getUserName());
		// ps.setString(2, user.getPassword());
		// ps.setLong(3, user.getGroupId());
		// ps.setString(4, user.getFirstName());
		// ps.setString(5, user.getSecondName());
		// ps.setString(6, user.getEmail());
		// ps.setString(7, user.getMobile());
		// ps.setLong(8, user.getUserId());
		//
		// ps.executeUpdate();
		// connection.commit();
	}

	public void deleteUser(User user) throws Exception {
		EntityManager em = null;
		try {
			em = getEntityManager();
			em.getTransaction().begin();
			em.remove(user);
			em.getTransaction().commit();
		} catch (Exception ex) {
			em.getTransaction().rollback();
			ex.printStackTrace();
			throw ex;
		} finally {
			if (em != null) {
				em.close();
			}
		}
		// connection = Connecting.getConnection();
		// String sql = "delete from users where userid=" + user.getUserId();
		// PreparedStatement ps = connection.prepareStatement(sql);
		//
		// ps.executeUpdate();
		// connection.commit();

	}

	public User getUser(String userName, String password) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select u from User u where u.userName=:userName and u.password=:password";
		return (User) em.createQuery(sql).setParameter("userName", userName)
				.setParameter("password", password).getSingleResult();

		// Map<String, Object> params = new HashMap<String, Object>();
		// params.put("userName", userName);
		// params.put("password", password);
		// return super.getEntity(User.class, params);
		// connection = Connecting.getConnection();
		// String sql = "select * from users  where username=? and password=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setString(1, userName);
		// ps.setString(2, password);
		// ResultSet rs = ps.executeQuery();
		// if (rs.next()) {
		// User user = new User();
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// return user;
		// }
		// return null;
	}

	public User getUserById(Long userId) throws Exception {

		EntityManager em = getEntityManager();
		String sql = "select u from User u where u.userId=:userId";
		return (User) em.createQuery(sql).setParameter("userId", userId)
				.getSingleResult();

		// connection = Connecting.getConnection();
		// String sql = "select * from users  where userid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, UserId);
		// ResultSet rs = ps.executeQuery();
		// if (rs.next()) {
		// User user = new User();
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// return user;
		// }
		// return null;
	}

	public List<User> getAllUsers() throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select u from User u ";
		return em.createQuery(sql).getResultList();
		// connection = Connecting.getConnection();
		// String sql = "select * from users";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		// List<User> ul = new ArrayList<User>();
		// while (rs.next()) {
		// User user = new User();
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// ul.add(user);
		// }
		// return ul;
	}

	public User getUser(String userName) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select u from User u where u.userName=:userName";
		return (User) em.createQuery(sql).setParameter("userName", userName)
				.getSingleResult();
		// connection = Connecting.getConnection();
		// String sql = "select * from users  where username=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setString(1, userName);
		// ResultSet rs = ps.executeQuery();
		// if (rs.next()) {
		// User user = new User();
		//
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// return user;
		// }
		// return null;
	}

	public User getUserByEmail(String email) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select u from User u where u.email=:email";
		return (User) em.createQuery(sql).setParameter("email", email)
				.getSingleResult();
		// connection = Connecting.getConnection();
		// String sql = "select * from users  where email=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setString(1, email);
		// ResultSet rs = ps.executeQuery();
		//
		// if (rs.next()) {
		// User user = new User();
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// return user;
		// }
		// return null;
	}

	public List<User> getListUserByGroupId(Long groupId) throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select u from User u where u.groupId=:groupId";
		return em.createQuery(sql).setParameter("groupId", groupId)
				.getResultList();
		// connection = Connecting.getConnection();
		// String sql = "select * from users  where groupid=?";
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ps.setLong(1, groupId);
		// ResultSet rs = ps.executeQuery();
		// List<User> ul = new ArrayList<User>();
		// while (rs.next()) {
		// User user = new User();
		// user.setUserId(rs.getLong("userid"));
		// user.setUserName(rs.getString("username"));
		// user.setPassword(rs.getString("password"));
		// user.setGroupId(rs.getLong("groupid"));
		// user.setFirstName(rs.getString("firstname"));
		// user.setSecondName(rs.getString("secondname"));
		// user.setEmail(rs.getString("email"));
		// user.setMobile(rs.getString("mobile"));
		// ul.add(user);
		// }
		// return ul;
	}

	public List<PrivilegeMapping> getPrivilegeMappingList(Long groupId)
			throws Exception {
		EntityManager em = getEntityManager();
		String sql = "select distinct gp.recid as recid,gp.group_id as groupId ,gp.privilage_id as privilegeId,gp.access_id as accessId,p.category_id as categoryId,p.name as privilegeName "
				+ " from group_privileges gp, privileges p  where gp.privilage_id=p.recid and gp.group_id="
				+ groupId;
		return (List<PrivilegeMapping>) (em.createNativeQuery(sql,
				PrivilegeMapping.class).getResultList());

		// return em.createQuery(sql).setParameter("groupId", groupId)
		// .getResultList();

		// connection = Connecting.getConnection();
		// String sql =
		// "select distinct gp.*,p.category_id,p.name from group_privileges gp, privileges p  where gp.privilage_id=p.recid and gp.group_id="
		// + groupId;
		// PreparedStatement ps = connection.prepareStatement(sql);
		// ResultSet rs = ps.executeQuery();
		// List<PrivilegeMapping> pml = new ArrayList<PrivilegeMapping>();
		// while (rs.next()) {
		// PrivilegeMapping pm = new PrivilegeMapping();
		// pm.setGroupId(groupId);
		// pm.setAccessId(rs.getLong("gp.access_id"));
		// pm.setPrivilegeId(rs.getLong("gp.privilage_id"));
		// pm.setCategoryId(rs.getLong("p.category_id"));
		// pm.setPrivilegeName(rs.getString("p.name"));
		// pml.add(pm);
		//
		// }
		// return pml;
	}

}
